# coding=utf-8
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine, text
from src.config.dbconfig import get_db_uri

db = SQLAlchemy()


def create_engine_():
    return create_engine(get_db_uri())


def query_sql(sql, param=None):
    engine = create_engine_()
    sql = text(sql)
    result = None
    with engine.connect() as connection:
        print(sql)
        result = connection.execute(sql, param)
        # data = [dict(zip(['id', 'category', 'price'], row)) for row in result]
    return result


def query_page(sql, param=None):
    """分页查询"""
    page = int(param.get("page"))
    limit = int(param.get("limit"))
    start = (page - 1) * limit
    page_sql = f'select * from ({sql}) as T LIMIT {start},{limit}'
    total_sql = f'select count(*) ct from ({sql})'
    result = query_sql(page_sql, param)
    total = query_sql(total_sql, param)
    return total, result


def begin_transaction():
    """start transaction"""
    db.session.begin()


def rollback_transaction():
    """rollback tran"""
    db.session.rollback()


def commit_transaction():
    """commit tran"""
    db.session.commit()


def del_model(model):
    db.session.commit()
    with db.session.begin():
        db.session.delete(model)
        db.session.commit()


def del_models(model):
    db.session.commit()
    with db.session.begin():
        db.session.delete_all(model)
        db.session.commit()


def add_model(model):
    db.session.commit()
    with db.session.begin():
        db.session.add(model)
        db.session.commit()


def add_models(model):
    db.session.commit()
    with db.session.begin():
        db.session.add_all(model)
        db.session.commit()


def add_and_not_commit(model):
    db.session.add(model)


def db_commit():
    db.session.commit()


def add_all(models):
    db.session.commit()
    db.session.add_all(models)
    db.session.commit()


def update_and_not_commit(model):
    """修改但是不提交"""
    db.session.commit()
    with db.session.begin():
        db.session.update(model)
        db.session.commit()
